<?php
/**
 * 生成mysql数据字典
 */

set_time_limit(0);
header("Content-type:text/html;charset=utf-8");
// 配置数据库
$database = array();
$database['DB_HOST'] = '10.15.200.169:3308';
$database['DB_NAME'] = 'ol_article';
$database['DB_USER'] = 'test';
$database['DB_PWD']  = 'reM0cK11XYki6IgP';
$mysql_conn = @mysql_connect("{$database['DB_HOST']}", "{$database['DB_USER']}", "{$database['DB_PWD']}") or die("Mysql connect is error.");
mysql_select_db($database['DB_NAME'], $mysql_conn);
mysql_query("set names gbk");

$primary 	    = array()   ; //带唯一主键的
$multiPrimary = array() ; //带多个主键的
$noPrimary 	  = array() ; //不带主键的
$result = mysql_query('show tables', $mysql_conn);

// 取得所有表名
while ($row = mysql_fetch_array($result))
{
  $tables[]['TABLE_NAME'] = $row[0];
}

// 循环取得所有表的备注及表中列消息
foreach($tables as $k => $v)
{
  $flag = 0;
  $tmp          = array() ; //临时记录
  $field        = array() ; //临时记录字段

  $sql  = " desc {$v['TABLE_NAME']} ";
  $table_result = mysql_query($sql, $mysql_conn);

  while ($t = mysql_fetch_assoc($table_result))
  {	  
       $field[$t['Field']] =  0; //记录字段,便于下面判断没有主键表里是否有id字段

      //判断主键还是复合主键 还是没有主键,进行归类放置
      if ($t['Key'] == 'PRI') {

        if ($t['Field'] == 'id'){
            $tmp[$t['Field']] = '#'.$t['Field'];
        } else {
            $tmp[$t['Field']] = $t['Field'];
        }

      } 
      
  }

     if( !empty($tmp)) {
         $field  = implode(',', $tmp) ;
      }

      if(count($tmp)>=2){

         //复合主键
         $multiPrimary[$v['TABLE_NAME']] = $field;

      } elseif (count($tmp) === 1) {

         //唯一主键
         $primary[$v['TABLE_NAME']] = $field ;

      } else {

          //没有主键处理，判断是否有id
          if (array_key_exists('id', $field)){
             $noPrimary[$v['TABLE_NAME']] = 'yes id' ;
          } else {
             $noPrimary[$v['TABLE_NAME']] = 'not id' ;
          }
        
      }
      unset($field);
      unset($tmp);

}

mysql_close($mysql_conn);

$html = '' ;
// if ( !empty($primary) && count($primary)>0 )
// {
//     $html .='<hr>';
//     $html .='<center style="color:red;font-size:18px">单主键</center>';
//     $html .='<hr>';
//     $html .= createHtml($primary,'');
// }

// if ( !empty($multiPrimary) && count($multiPrimary)>0)
// {
//     $html .='<hr>';
//     $html .='<center style="color:red;font-size:18px">复合主键</center>';
//     $html .='<hr>';
//     $html .= createHtml($multiPrimary,'');
// }

if ( !empty($noPrimary) && count($noPrimary)>0 )
{ 
    $html .='<hr>';
    $html .='<center style="color:red;font-size:18px">没有主键</center>';
    $html .='<hr>';
    $html .= createHtml($noPrimary,'');
    
}


/* 生成word */
// header ( "Content-type:application/vnd.ms-word" );
// header ( "Content-Disposition:attachment;filename={$database['DB_NAME']}数据字典.doc" );
/* 生成excel*/
// header ( "Content-type:application/vnd.ms-excel" );
// header ( "Content-Disposition:attachment;filename={$database['DB_NAME']}数据字典.xls" );
// 输出
echo '<html>
  <meta charset="utf-8">
  <title>自动生成数据字典</title>
  <style>
    body,td,th {font-family:"宋体"; font-size:12px;}
    table,h1,p{width:960px;margin:0px auto;}
    table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;}
    table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; }
    table th{text-align:left; font-weight:bold;height:26px; line-height:26px; font-size:12px; border:1px solid #CCC;padding-left:5px;}
    table td{height:20px; font-size:12px; border:1px solid #CCC;background-color:#fff;padding-left:5px;}
    .c1{ width: 150px;}
    .c2{ width: 150px;}
    .c3{ width: 80px;}
    .c4{ width: 100px;}
    .c5{ width: 100px;}
    .c6{ width: 300px;}
  </style>
  <body>';
echo '<h1 style="text-align:center;">'.$database['DB_NAME'].'数据字典</h1>';
echo '<p  style="text-align:center;margin:20px auto;">生成时间：' . date('Y-m-d H:i:s') . '</p>';
echo $html;
echo '<p style="text-align:left;margin:20px auto;">总共：' . count($tables) . '个数据表</p>';
echo '</body></html>';

function createHtml($result,$subjoin='')
{ 
    $html = '';
    foreach($result as $k => $v)
    {
      $html .= '<table border="1" cellspacing="0" cellpadding="0" align="center">';
      $html .= '<caption >表名：' . $k .'</caption>';
      $html .= '<tbody><tr><th>字段名</th></tr>';
      $html .= '';

      $html .= '<td class="c1">' . $v . '</td>';
      $html .= '</tr>';

      $html .= '</tbody></table></p>';
    }
    return $html ;
}

function p($arr)
{
	echo '<pre>';
	print_r($arr);
}
?>